<?php

namespace Home\Service;

require __DIR__ . '/../Common/Excel/PHPExcel/IOFactory.php';

/**
 * 销售报表 Service
 *
 * @author 李静波
 */
class SalesReportService extends PSIBaseService {

	public function salesOrderListQueryData($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$fromDT = $params["fromDT"];
		$toDT = $params["toDT"];
		$customerId = $params["customerId"];
		$goodsId = $params["goodsId"];
		$bizUserId = $params["bizUserId"];
		$ref = $params["ref"];
		$dealFromDT = $params["dealFromDT"];
		$dealToDT = $params["dealToDT"];
		$status = $params["status"];
		
		$start = $params["start"];
		$limit = $params["limit"];
		
		$db = M();
		$result = array();
		$queryParams = array();
		
		$sql = "select g.code, g.name, g.spec, u.name as unit_name,
					s.ref, s.biz_dt, s.deal_date,
					d.qc_begin_dt, d.qc_end_dt, d.expiration,
					d.goods_count, d.goods_price, d.goods_money,
					us.name as biz_user_name, c.name as customer_name,
					s.id
				from t_so_bill s, t_so_bill_detail d, 
					t_goods g, t_goods_unit u, t_user us, t_customer c
				where (s.id = d.sobill_id) and (d.goods_id = g.id)
					and (g.unit_id = u.id) and (s.biz_user_id = us.id)
					and (s.customer_id = c.id) ";
		
		if ($fromDT) {
			$sql .= " and (s.biz_dt >= '%s') ";
			$queryParams[] = $fromDT;
		}
		if ($toDT) {
			$sql .= " and (s.biz_dt <= '%s') ";
			$queryParams[] = $toDT;
		}
		if ($customerId) {
			$sql .= " and (s.customer_id = '%s') ";
			$queryParams[] = $customerId;
		}
		if ($goodsId) {
			$sql .= " and (d.goods_id = '%s') ";
			$queryParams[] = $goodsId;
		}
		if ($bizUserId) {
			$sql .= " and (s.biz_user_id = '%s') ";
			$queryParams[] = $bizUserId;
		}
		if ($ref) {
			$sql .= " and (s.ref like '%s') ";
			$queryParams[] = "%{$ref}%";
		}
		if ($dealFromDT) {
			$sql .= " and (s.deal_date >= '%s') ";
			$queryParams[] = $dealFromDT;
		}
		if ($dealToDT) {
			$sql .= " and (s.deal_date <= '%s') ";
			$queryParams[] = $dealToDT;
		}
		if ($status != - 1) {
			$sql .= " and (s.id in (
						select sows.so_id
						from t_so_ws sows, t_ws_bill wsBill
						where (sows.ws_id = wsBill.id) 
							and (wsBill.bill_status = %d) 
					)) ";
			$queryParams[] = $status;
		}
		
		$sql .= " order by g.code, s.ref desc
				limit %d, %d";
		$queryParams[] = $start;
		$queryParams[] = $limit;
		
		$data = $db->query($sql, $queryParams);
		foreach ( $data as $v ) {
			$item = array(
					"goodsCode" => $v["code"],
					"goodsName" => $v["name"],
					"goodsSpec" => $v["spec"],
					"ref" => $v["ref"],
					"bizDT" => $this->toYMD($v["biz_dt"]),
					"goodsCount" => $v["goods_count"],
					"goodsPrice" => $v["goods_price"],
					"goodsMoney" => $v["goods_money"],
					"dealDT" => $this->toYMD($v["deal_date"]),
					"bizUserName" => $v["biz_user_name"],
					"customerName" => $v["customer_name"],
					"unitName" => $v["unit_name"]
			);
			
			$qcBeginDT = $this->toYmdForQC($v["qc_begin_dt"]);
			if ($qcBeginDT) {
				$item["qcBeginDT"] = $qcBeginDT;
			}
			$expiration = $v["expiration"];
			if ($expiration) {
				$item["expiration"] = $expiration;
			}
			$qcEndDT = $this->toYmdForQC($v["qc_end_dt"]);
			if ($qcEndDT) {
				$item["qcEndDT"] = $qcEndDT;
			}
			
			$soId = $v["id"];
			
			$sql = "select w.bill_status
					from t_so_ws s, t_ws_bill w
					where s.so_id = '%s' and s.ws_id = w.id";
			$d = $db->query($sql, $soId);
			if ($d) {
				$billStatus = $d[0]["bill_status"];
				if ($billStatus == 0) {
					$item["status"] = "未出库";
				} else if ($billStatus == 1000) {
					$item["status"] = "已出库";
				} else {
					$item["status"] = "";
				}
			} else {
				$item["status"] = "订单未审核";
			}
			
			$result[] = $item;
		}
		
		$sql = "select count(*) as cnt
				from t_so_bill s, t_so_bill_detail d, 
					t_goods g, t_goods_unit u
				where (s.id = d.sobill_id) and (d.goods_id = g.id)
					and (g.unit_id = u.id)";
		$queryParams = array();
		if ($fromDT) {
			$sql .= " and (s.biz_dt >= '%s') ";
			$queryParams[] = $fromDT;
		}
		if ($toDT) {
			$sql .= " and (s.biz_dt <= '%s') ";
			$queryParams[] = $toDT;
		}
		if ($customerId) {
			$sql .= " and (s.customer_id = '%s') ";
			$queryParams[] = $customerId;
		}
		if ($goodsId) {
			$sql .= " and (d.goods_id = '%s') ";
			$queryParams[] = $goodsId;
		}
		if ($bizUserId) {
			$sql .= " and (s.biz_user_id = '%s') ";
			$queryParams[] = $bizUserId;
		}
		if ($ref) {
			$sql .= " and (s.ref like '%s') ";
			$queryParams[] = "%{$ref}%";
		}
		if ($dealFromDT) {
			$sql .= " and (s.deal_date >= '%s') ";
			$queryParams[] = $dealFromDT;
		}
		if ($dealToDT) {
			$sql .= " and (s.deal_date <= '%s') ";
			$queryParams[] = $dealToDT;
		}
		if ($status != - 1) {
			$sql .= " and (s.id in (
						select sows.so_id
						from t_so_ws sows, t_ws_bill wsBill
						where (sows.ws_id = wsBill.id) 
							and (wsBill.bill_status = %d) 
					)) ";
			$queryParams[] = $status;
		}
		$data = $db->query($sql, $queryParams);
		$cnt = $data[0]["cnt"];
		
		return array(
				"dataList" => $result,
				"totalCount" => $cnt
		);
	}

	private function genDataForsalesOrderListExcel($params) {
		$fromDT = $params["fromDT"];
		$toDT = $params["toDT"];
		$customerId = $params["customerId"];
		$goodsId = $params["goodsId"];
		$bizUserId = $params["bizUserId"];
		$ref = $params["ref"];
		$dealFromDT = $params["dealFromDT"];
		$dealToDT = $params["dealToDT"];
		$status = $params["status"];
		
		$db = M();
		$result = array();
		$queryParams = array();
		
		$sql = "select g.code, g.name, g.spec, u.name as unit_name,
					s.ref, s.biz_dt, s.deal_date,
					d.qc_begin_dt, d.qc_end_dt, d.expiration,
					d.goods_count, d.goods_price, d.goods_money,
					us.name as biz_user_name, c.name as customer_name,
					s.id
				from t_so_bill s, t_so_bill_detail d, 
					t_goods g, t_goods_unit u, t_user us, t_customer c
				where (s.id = d.sobill_id) and (d.goods_id = g.id)
					and (g.unit_id = u.id) and (s.biz_user_id = us.id)
					and (s.customer_id = c.id) ";
		
		if ($fromDT) {
			$sql .= " and (s.biz_dt >= '%s') ";
			$queryParams[] = $fromDT;
		}
		if ($toDT) {
			$sql .= " and (s.biz_dt <= '%s') ";
			$queryParams[] = $toDT;
		}
		if ($customerId) {
			$sql .= " and (s.customer_id = '%s') ";
			$queryParams[] = $customerId;
		}
		if ($goodsId) {
			$sql .= " and (d.goods_id = '%s') ";
			$queryParams[] = $goodsId;
		}
		if ($bizUserId) {
			$sql .= " and (s.biz_user_id = '%s') ";
			$queryParams[] = $bizUserId;
		}
		if ($ref) {
			$sql .= " and (s.ref like '%s') ";
			$queryParams[] = "%{$ref}%";
		}
		if ($dealFromDT) {
			$sql .= " and (s.deal_date >= '%s') ";
			$queryParams[] = $dealFromDT;
		}
		if ($dealToDT) {
			$sql .= " and (s.deal_date <= '%s') ";
			$queryParams[] = $dealToDT;
		}
		if ($status != - 1) {
			$sql .= " and (s.id in (
						select sows.so_id
						from t_so_ws sows, t_ws_bill wsBill
						where (sows.ws_id = wsBill.id) 
							and (wsBill.bill_status = %d) 
					)) ";
			$queryParams[] = $status;
		}
		
		$sql .= " order by g.code, s.ref desc ";
		
		$data = $db->query($sql, $queryParams);
		foreach ( $data as $v ) {
			$item = array(
					"goodsCode" => $v["code"],
					"goodsName" => $v["name"],
					"goodsSpec" => $v["spec"],
					"ref" => $v["ref"],
					"bizDT" => $this->toYMD($v["biz_dt"]),
					"goodsCount" => $v["goods_count"],
					"goodsPrice" => $v["goods_price"],
					"goodsMoney" => $v["goods_money"],
					"dealDT" => $this->toYMD($v["deal_date"]),
					"bizUserName" => $v["biz_user_name"],
					"customerName" => $v["customer_name"],
					"unitName" => $v["unit_name"]
			);
			
			$qcBeginDT = $this->toYmdForQC($v["qc_begin_dt"]);
			if ($qcBeginDT) {
				$item["qcBeginDT"] = $qcBeginDT;
			}
			$expiration = $v["expiration"];
			if ($expiration) {
				$item["expiration"] = $expiration;
			}
			$qcEndDT = $this->toYmdForQC($v["qc_end_dt"]);
			if ($qcEndDT) {
				$item["qcEndDT"] = $qcEndDT;
			}
			
			$soId = $v["id"];
			
			$sql = "select w.bill_status
					from t_so_ws s, t_ws_bill w
					where s.so_id = '%s' and s.ws_id = w.id";
			$d = $db->query($sql, $soId);
			if ($d) {
				$billStatus = $d[0]["bill_status"];
				if ($billStatus == 0) {
					$item["status"] = "未出库";
				} else if ($billStatus == 1000) {
					$item["status"] = "已出库";
				} else {
					$item["status"] = "";
				}
			} else {
				$item["status"] = "订单未审核";
			}
			
			$result[] = $item;
		}
		
		return $result;
	}

	public function salesOrderListExcel($json) {
		if ($this->isNotOnline()) {
			return;
		}
		
		$params = json_decode(html_entity_decode($json), true);
		
		if ($params == null) {
			return;
		}
		
		$data = $this->genDataForsalesOrderListExcel($params);
		
		$totalRow = count($data);
		
		// 导出Excel
		$excel = new \PHPExcel();
		
		$sheet = $excel->getActiveSheet();
		if (! $sheet) {
			$sheet = $excel->createSheet();
		}
		
		$sheet->setTitle("销售订单跟踪表");
		
		$sheet->getRowDimension('1')->setRowHeight(22);
		$sheet->setCellValue("A1", "销售订单跟踪表");
		
		$sheet->getColumnDimension('A')->setWidth(15);
		$sheet->setCellValue("A2", "商品编码");
		
		$sheet->getColumnDimension('B')->setWidth(40);
		$sheet->setCellValue("B2", "品名");
		
		$sheet->getColumnDimension('C')->setWidth(20);
		$sheet->setCellValue("C2", "规格型号");
		
		$sheet->getColumnDimension('D')->setWidth(10);
		$sheet->setCellValue("D2", "生产日期");
		
		$sheet->getColumnDimension('E')->setWidth(15);
		$sheet->setCellValue("E2", "保质期(天)");
		
		$sheet->getColumnDimension('F')->setWidth(10);
		$sheet->setCellValue("F2", "到期日期");
		
		$sheet->getColumnDimension('G')->setWidth(10);
		$sheet->setCellValue("G2", "订单日期");
		
		$sheet->getColumnDimension('H')->setWidth(15);
		$sheet->setCellValue("H2", "销售订单号");
		
		$sheet->getColumnDimension('I')->setWidth(15);
		$sheet->setCellValue("I2", "业务员");
		
		$sheet->getColumnDimension('J')->setWidth(15);
		$sheet->setCellValue("J2", "客户");
		
		$sheet->getColumnDimension('K')->setWidth(15);
		$sheet->setCellValue("K2", "出库状态");
		
		$sheet->getColumnDimension('L')->setWidth(10);
		$sheet->setCellValue("L2", "销售数量");
		
		$sheet->getColumnDimension('M')->setWidth(10);
		$sheet->setCellValue("M2", "单位");
		
		$sheet->getColumnDimension('N')->setWidth(10);
		$sheet->setCellValue("N2", "销售单价");
		
		$sheet->getColumnDimension('O')->setWidth(10);
		$sheet->setCellValue("O2", "销售金额");
		
		$sheet->getColumnDimension('P')->setWidth(10);
		$sheet->setCellValue("P2", "发货日期");
		
		foreach ( $data as $i => $v ) {
			$row = $i + 3;
			$sheet->setCellValue("A" . $row, $v["goodsCode"]);
			$sheet->setCellValue("B" . $row, $v["goodsName"]);
			$sheet->setCellValue("C" . $row, $v["goodsSpec"]);
			$sheet->setCellValue("D" . $row, $v["qcBeginDT"]);
			$sheet->setCellValue("E" . $row, $v["expiration"]);
			$sheet->setCellValue("F" . $row, $v["qcEndDT"]);
			$sheet->setCellValue("G" . $row, $v["bizDT"]);
			$sheet->setCellValue("H" . $row, $v["ref"]);
			$sheet->setCellValue("I" . $row, $v["bizUserName"]);
			$sheet->setCellValue("J" . $row, $v["customerName"]);
			$sheet->setCellValue("K" . $row, $v["status"]);
			$sheet->setCellValue("L" . $row, $v["goodsCount"]);
			$sheet->setCellValue("M" . $row, $v["unitName"]);
			$sheet->setCellValue("N" . $row, $v["goodsPrice"]);
			$sheet->setCellValue("O" . $row, $v["goodsMoney"]);
			$sheet->setCellValue("P" . $row, $v["dealDT"]);
		}
		
		// 画表格边框
		$styleArray = array(
				'borders' => array(
						'allborders' => array(
								'style' => 'thin'
						)
				)
		);
		$lastRow = count($data) + 2;
		$sheet->getStyle('A2:P' . $lastRow)->applyFromArray($styleArray);
		
		$fileName = "销售订单跟踪表";
		
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
		header('Cache-Control: max-age=0');
		
		$writer = \PHPExcel_IOFactory::createWriter($excel, "Excel5");
		$writer->save("php://output");
	}
}